
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('fnIter_intlist_to_table')
          AND TYPE IN ('P','PC'))
DROP FUNCTION fnIter_intlist_to_table
GO
/****** Object:  UserDefinedFunction [dbo].[fnIter_intlist_to_table]    Script Date: 10/22/2010 17:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnIter_intlist_to_table] (@list ntext)
  RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                      number  int NOT NULL) AS
BEGIN
  DECLARE @pos      int,
          @textpos  int,
          @chunklen smallint,
          @str      nvarchar(4000),
          @tmpstr   nvarchar(4000),
          @leftover nvarchar(4000)

  SET @textpos = 1
  SET @leftover = ''
  WHILE @textpos <= datalength(@list) / 2
  BEGIN
     SET @chunklen = 4000 - datalength(@leftover) / 2
     SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
     SET @textpos = @textpos + @chunklen

     SET @pos = charindex(',', @tmpstr)
     WHILE @pos > 0
     BEGIN
        SET @str = substring(@tmpstr, 1, @pos - 1)
        INSERT @tbl (number) VALUES(convert(int, @str))
        SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
        SET @pos = charindex(',', @tmpstr)
     END

     SET @leftover = @tmpstr
  END

  IF ltrim(rtrim(@leftover)) <> ''
     INSERT @tbl (number) VALUES(convert(int, @leftover))

  RETURN
END

GO
